using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;

using Krystalware.SlickUpload;
using Krystalware.SlickUpload.Configuration;
using Krystalware.SlickUpload.Storage;

namespace AspNetMvcRazorCs.Storage
{
    public class CustomSqlUploadStreamProvider : SqlClientUploadStreamProvider
    {
        public CustomSqlUploadStreamProvider(UploadStreamProviderElement settings)
            : base(settings)
		{ }

        /// <summary>
        /// Inserts the record for the given file, and returns the id of the record inserted.
        /// </summary>
        /// <remarks>This is a very verbose implementation, because it supports every possible data type and doesn't use an ORM. When implementing in your solution,
        /// we recommend you rewrite using whatever data access layer you use.</remarks>
        public override string InsertRecord(UploadedFile file, IDbConnection cn, IDbTransaction t)
        {
            string category = file.UploadRequest.Data["fileCategory"];

            using (SqlCommand cmd = (SqlCommand)cn.CreateCommand())
            {
                StringBuilder insertCommand = new StringBuilder();

                insertCommand.Append("INSERT INTO ");
                insertCommand.Append(Table);
                insertCommand.Append(" (");
                insertCommand.Append(DataField);

                if (FileNameField != null)
                {
                    insertCommand.Append(",");
                    insertCommand.Append(FileNameField);
                }

                insertCommand.Append(",");
                insertCommand.Append("Category");

                if (DataType != SqlColumnDataType.FileStream)
                {
                    insertCommand.Append(") VALUES (NULL");
                }
                else
                {
                    insertCommand.Append(") OUTPUT INSERTED." + KeyField);
                    insertCommand.Append(" VALUES (CAST('' AS varbinary(MAX))");
                }

                if (FileNameField != null)
                {
                    insertCommand.Append(",@fileName");

                    SqlParameter fileNameParm = cmd.CreateParameter();

                    fileNameParm.ParameterName = "@fileName";
                    fileNameParm.DbType = DbType.String;
                    fileNameParm.Value = file.ClientName;

                    cmd.Parameters.Add(fileNameParm);
                }

                insertCommand.Append(",@category");

                SqlParameter categoryParm = cmd.CreateParameter();

                categoryParm.ParameterName = "@category";
                categoryParm.DbType = DbType.String;
                categoryParm.Value = category;

                cmd.Parameters.Add(categoryParm);

                insertCommand.Append(");");

                if (DataType != SqlColumnDataType.FileStream)
                    insertCommand.Append("SELECT SCOPE_IDENTITY();");

                cmd.CommandText = insertCommand.ToString();
                cmd.Transaction = t as SqlTransaction;

                try
                {
                    if (cn.State != ConnectionState.Open)
                        cn.Open();

                    return cmd.ExecuteScalar().ToString();
                }
                finally
                {
                    if (t == null)
                        cn.Close();
                }
            }
        }
    }
}